﻿#region Library
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
#endregion

namespace ConnectDatabase
{
    public class ConnectDB
    {
        #region Attributes
        string connectionString;
        #endregion

        //====================================================================================
        #region General Method
        /// <summary>
        /// Initializes a new instance of the <see cref="ConnectDB"/> class.
        /// </summary>
        /// <param name="connString">The connection string.</param>
        public ConnectDB(string connString)
        {
            this.connectionString = connString;
        }
        SqlConnection getConnect()
        {
            return new SqlConnection(connectionString);
        }
        /// <summary>
        /// Gets the table.
        /// </summary>
        /// <param name="sql">The SQL.</param>
        /// <returns></returns>
        public DataTable getTable(string sql)//Get table function
        {
            try
            {
                DataTable table = new DataTable();
                SqlConnection con = getConnect();
                SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, con);
                dataAdapter.Fill(table);
                return table;
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <param name="sql">The SQL.</param>
        public void executeNonQuery(string sql)//Execute one statement without value return
        {
            try
            {
                SqlConnection con = getConnect();
                con.Open();
                SqlCommand cmd = new SqlCommand(sql, con);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                con.Close();
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                throw ex;
            }
        }
        #endregion

        //====================================================================================
        #region Support Method
        SqlDataAdapter dataAdapter(string sql)//DataAdapter
        {
            SqlConnection con = getConnect();
            return new SqlDataAdapter(sql, con);
        }
        #endregion

        //====================================================================================
        #region Method support for Report part of SMS Project
        public DataTable getReportYear(string year)
        {
            try
            {
                DataTable table = getTable("proReportYear " + year);

                return table;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable getReportMonth(string year, string month)
        {
            try
            {
                DataTable table = getTable("proReportMonth " + year + "," + month);

                return table;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion
    }
}
